package com.tianxiabuyi.persist;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.StatelessSession;


/**
 * 本类主要作用是进行数据库的查询封装<br>
 * 考虑以后直接结合Mongodb的查询封装，或者建立类似本类的使用方法，<br>
 * 关于大数据表的快速索引计数，考虑无缝接入，所有的增删改，都必须和快速计数进行结合，也就是所有的改动操作必须走接口<br>
 * （增，删比较好弄（删可以取出所有目标id先处理，然后删除），改需要细节考虑，因为牵涉到批量改动某未知字段）<br>
 * 增加多数据源<br>
 * 查询的两种用法：db("name"),name为数据源名称<br>
 * 1.  List list = QBuilder.db("basedb").queryHql("from user where hospital=1010");<br>
 * 2.  List list = QBuilder.db("365db").select().from("euser as eu, userprofile as up").where(new Where("eu.id=up.uid").and("eu.src=", hospital).and("eu.screenname=",formatName)).page(1,1).query().getResultObj();<br>
 * 其它删除更新操作可以用execHQL(hql)
 */
public class QBuilder {
    SessionFactory sf;
    String preFrom;
    String from;
    Integer type = 0;// 0=select 1=delete 2=update
    String setKey;
    String setValue;
    Where where = new Where();
    Integer skip;
    Integer limit;
    Boolean isCount;
    String order = "";
    String group = "";
    String having = "";
    String counter = "count(*)";
    public final static String desc = "DESC";
    public final static String asc = "ASC";

    private DBTools dbTools;

    /**
     * 根据数据源名称创建QBuilder对象
     * @param dbName 数据源名称
     * @return QBuilder对象
     */
    public static QBuilder db(String dbName) {
        QBuilder q = new QBuilder();
        q.dbTools = DBTools.db(dbName);
        return q;
    }

    public QBuilder initSF(SessionFactory sf) {
        this.sf = sf;
        return this;
    }

    public QBuilder(SessionFactory sf) {
        this.sf = sf;
    }

    private QBuilder() {

    }

    public QBuilder where(Where where) {
        this.where = where;
        return this;
    }

    public QBuilder where(String where) {
        this.where = new Where(where);
        return this;
    }

    public QBuilder where(String cond, String value) {
        this.where = new Where(cond, value);
        return this;
    }

    public QBuilder order(String order, String otype) {
        if (type == 0) {
            this.order = " order by " + order + " " + otype;
        }
        return this;
    }

    public QBuilder counterField(String counter) {
        this.counter = "count(" + counter + ")";
        return this;

    }

    public QBuilder group(String group) {
        if (type == 0) {
            this.group = " group by " + group;
        }
        return this;
    }

    public QBuilder having(String having) {
        if (type == 0) {
            this.having = " having " + having;
        }
        return this;
    }

    public QBuilder skip(Integer skip) {
        if (type == 0) {
            this.skip = skip;
        }
        return this;
    }

    public QBuilder limit(Integer limit) {
        if (type == 0) {
            this.limit = limit;
        }
        return this;
    }

    public QBuilder isCount(Boolean isCount) {
        this.isCount = isCount;
        return this;
    }

    public QBuilder page(Integer itemCount, Integer pageNum) {
        if (type == 0) {
            if ((itemCount != null) && (itemCount > 0)) {
                if ((pageNum != null) && (pageNum > 0)) {
                    skip = (pageNum - 1) * itemCount;
                    limit = itemCount;
                }
            }
        }
        return this;
    }

    public QBuilder select() {
        this.setPreFrom("");
        this.setType(0);
        return this;
    }

    public QBuilder select(String select) {
        this.setPreFrom("select " + PersistTools.getDesc(select, ""));
        this.setType(0);
        return this;
    }

    public QBuilder from(String from) {
        if ((type == 0) || (type == 1)) {
            this.from = " from " + PersistTools.getDesc(from, "");
        }
        return this;
    }

    public QBuilder set(String set) {
        if ((type == 2)) {
            setPreFrom(preFrom + " set " + PersistTools.getDesc(set, ""));
        }
        return this;
    }

    public QBuilder delete() {
        this.setPreFrom("delete ");
        this.setType(2);
        return this;
    }

    public QBuilder update(String update) {
        this.setPreFrom("update " + PersistTools.getDesc(update, ""));
        this.setType(2);
        return this;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public String getPreFrom() {
        return preFrom;
    }

    public void setPreFrom(String preFrom) {
        this.preFrom = preFrom;
    }

    public String getFrom() {
        return from;
    }

    public void setFrom(String from) {
        this.from = from;
    }

    public int execHQL(String hql) {
        StatelessSession ss = this.dbTools.getSf().openStatelessSession();
        try {
            ss.beginTransaction();
            int result = ss.createQuery(hql).executeUpdate();
            ss.getTransaction().commit();
            ss.close();
            return result;
        } catch (Exception ex) {
            ss.close();
            ex.printStackTrace();
            return -1;
        }
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public <T> List<T> queryHql(String hql) {
        StatelessSession ss = this.dbTools.getSf().openStatelessSession();
        try {
            ss.beginTransaction();
            List<T> list = ss.createQuery(hql).list();
            ss.getTransaction().commit();
            ss.close();
            return list;
        } catch (Exception ex) {
            ss.close();
            ex.printStackTrace();
            return new java.util.ArrayList();
        }
    }

    public <T> List<T> queryHql(String hql, Integer limit) {
        return queryHql(hql, limit, 1);
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public <T> List<T> queryHql(String hql, Integer itemCount, Integer pageNum) {
        StatelessSession ss = this.dbTools.getSf().openStatelessSession();
        try {
            Integer offSet = 0;
            Integer limit = null;

            if ((itemCount == null) || (itemCount <= 0)) {
                pageNum = null;
            } else {
                limit = itemCount;
                if ((pageNum != null) && (pageNum > 0)) {
                    offSet = (pageNum - 1) * itemCount;
                }
            }

            ss.beginTransaction();
            Query query = ss.createQuery(hql).setFirstResult(offSet);
            if (limit != null) {
                query = query.setMaxResults(limit);
            }
            List list = query.list();
            ss.getTransaction().commit();
            ss.close();
            return list;
        } catch (Exception ex) {
            ss.close();
            ex.printStackTrace();
            return null;
        }
    }

    public Integer exec() {
        StatelessSession ss = this.dbTools.getSf().openStatelessSession();
        try {
            ss.beginTransaction();
            String sqlWhere = PersistTools.getDesc(where.getWhere(), "");
            HashMap<Integer, Object> params = where.getParams();

            String hql = preFrom + " " + from + " " + sqlWhere;
            if (type == 2) {
                hql = preFrom + " " + sqlWhere;
                // TODO 加入预先取出目标id列表，然后调用是否进行快速索引/全文索引同步处理的代码
            }
            Query query = ss.createQuery(hql);
            for (Integer i = 0; i < params.size(); i++) {
                Object value = params.get(i + 1);
                if (value instanceof Integer) {
                    query.setInteger(i, (Integer) value);
                } else if (value instanceof Long) {
                    query.setLong(i, (Long) value);
                } else {
                    query.setString(i, "" + value);
                }

            }
            Integer result = query.executeUpdate();
            ss.getTransaction().commit();
            ss.close();
            return result;
        } catch (Exception ex) {
            ss.close();
            ex.printStackTrace();
            return -1;
        }
    }

    @SuppressWarnings("rawtypes")
    public CommonResult<List> query() {
        String sqlWhere = PersistTools.getDesc(where.getWhere(), "");
        HashMap<Integer, Object> params = where.getParams();

        if ((sqlWhere != null) && (sqlWhere.trim().length() > 0)) {
            sqlWhere = " where " + sqlWhere;
        } else {
            sqlWhere = " ";
        }
        String hql = preFrom + " " + from + " " + sqlWhere + " " + order + " "
                + group + " " + having;
        String countHql = "select " + counter + " " + from + " " + sqlWhere
                + " " + group + " " + having;

        StatelessSession ss = this.dbTools.getSf().openStatelessSession();
        try {
            Query query = ss.createQuery(hql);
            Query cQuery = ss.createQuery(countHql);

            for (Map.Entry<Integer, Object> entry : params.entrySet()) {
                Integer key = entry.getKey();
                Object value = entry.getValue();
                if (value instanceof Integer) {
                    query.setInteger(key +"", (Integer) value);
                    cQuery.setInteger(key +"", (Integer) value);
                } else if (value instanceof Long) {
                    query.setLong(key +"", (Long) value);
                    cQuery.setLong(key +"", (Long) value);
                } else {
                    query.setString(key +"",  value + "");
                    cQuery.setString(key +"", value + "");
                }
            }

            boolean isPaged = false;

            if ((skip != null) && (skip >= 0)) {
                if ((limit != null) && (limit > 0)) {
                    query.setFirstResult(skip).setMaxResults(limit);
                    isPaged = true;
                }
            }
            List list = query.list();
            Long count = 0l;
            List cList = cQuery.list();
            if (!isPaged) {
                count = list.size() + 0l;
            } else {
                count = PersistTools.getInt("" + (cList).get(0)) + 0l;
                if ((group.trim().length() > 5) && (cList.size() > 1)) {
                    count = cList.size() + 0l;
                }
            }

            CommonResult<List> result = new CommonResult<List>();
            result.extInfo.put("count", count);
            result.setResultObj(list);
            ss.close();
            return result;
        } catch (Exception ex) {
            ss.close();
            ex.printStackTrace();
            CommonResult<List> result = new CommonResult<List>();
            result.extInfo.put("count", 0);
            result.setResultObj(new java.util.ArrayList());
            return result;
        }
    }

    public void set(String key, String value) {
        this.setKey = key;
        this.setValue = value;
    }

    public String getSetKey() {
        return setKey;
    }

    public void setSetKey(String setKey) {
        this.setKey = setKey;
    }

    public String getSetValue() {
        return setValue;
    }

    public void setSetValue(String setValue) {
        this.setValue = setValue;
    }
}
